package com.artup.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.artup.pojo.TrolleyDetail;

/**
 * 购物车详情
 * @author hapday
 * @date 2017年7月17日 @Time 下午5:27:40
 */
public interface TrolleyDetailDao {
	/**
	 * 添加【购物车详情】
	 * @param trolleyDetail 购物车详情
	 * @throws Exception
	 */
	@Insert( value = { "INSERT INTO bud_car (db_id, edt_db_id, user_db_id, price, num, discount, total, created_dt, channel_code, CLIENT, sku_code)"
			+ " VALUES (#{id, javaType=String, jdbcType=VARCHAR}, #{worksId, javaType=String, jdbcType=VARCHAR}, #{passportId, javaType=Integer, jdbcType=INTEGER}"
			+ ", #{price, javaType=Float, jdbcType=FLOAT}, #{quantity, javaType=Integer, jdbcType=INTEGER}, #{discountSubtotal, javaType=Float, jdbcType=FLOAT}"
			+ ", #{subtotal, javaType=Float, jdbcType=FLOAT}, NOW(), #{channelCode, javaType=String, jdbcType=VARCHAR}, #{clientCode, javaType=String, jdbcType=VARCHAR}"
			+ ", #{skuCode, javaType=String, jdbcType=VARCHAR})" } )
	void insertTrolleyDetail(TrolleyDetail trolleyDetail) throws Exception;
	
	/**
	 * 根据【通行证ID】查询【购物车详情列表】
	 * @param passportId 【通行证ID】
	 * @return 【购物车详情列表】
	 */
	@Select( value = { "SELECT"
			+ " td.db_id AS id, td.edt_db_id AS worksId, td.price price, td.num quantity,"
			+ " td.total AS subtotal, td.discount AS discountSubtotal, td.created_dt AS createTime"
			+ ", td.sku_code AS skuCode, td.order_id AS orderId"
//			+ ", address_id AS addressId, invoice_id AS invoiceId"
			+ ", (SELECT w.name FROM bud_edit w WHERE w.db_id = td.edt_db_id) worksName"
			+ ", (SELECT w.size FROM bud_edit w WHERE w.db_id = td.edt_db_id) worksSize"
			+ ", (SELECT w.shape FROM bud_edit w WHERE w.db_id = td.edt_db_id) worksShape"
			+ ", (SELECT w.color FROM bud_edit w WHERE w.db_id = td.edt_db_id) worksColor"
			+ ", (SELECT w.category FROM bud_edit w WHERE w.db_id = td.edt_db_id) worksTypeCode"
			+ ", (SELECT w.thumbnail_image_url FROM bud_edit w WHERE w.db_id = td.edt_db_id) worksThumbnailUrl"
			+ " FROM bud_car td"
			+ " WHERE"
			+ "		  status = 1 AND td.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER}"
			+ " ORDER BY td.created_dt DESC LIMIT #{offset, javaType=Long, jdbcType=BIGINT}, #{pageSize, javaType=Integer, jdbcType=INTEGER}" } )
	List<TrolleyDetail> selectTrolleyDetailListByPassportId(@Param("passportId") Integer passportId, @Param("offset") long offset, @Param("pageSize") int pageSize) throws SQLException;
	
	/**
	 * 根据【IDs】查询【购物车详情】列表
	 * @param ids 【IDs】
	 * @return 查询【购物车详情】列表
	 * @throws SQLException
	 */
	@Select( value = { "<script>SELECT td.order_id AS orderId, td.edt_db_id AS worksId, td.num AS quantity"
			+ " FROM bud_car td WHERE td.db_id IN "
			+ "<foreach collection='ids' item='id' open='(' close=')' separator=','>"
				+ "#{id, javaType=String, jdbcType=VARCHAR}"
			+ "</foreach>"
			+ " </script>" } )
	public List<TrolleyDetail> selectTrolleyDetailListByIds(@Param("ids") String [] ids) throws SQLException;

	/**
	 * 根据【产品ID】修改【购物车数量】
	 * @param passportId 【通行证ID】
	 * @param edtId 【作品ID】 
	 * @param num 【购物车数量】
	 */
	@Update("update bud_car set num = #{num}"
			+ " where user_db_id = #{passportId}"
			+ " and edt_db_id = #{edtId}")
	int updateTrolleyNum(@Param("passportId") String passportId,@Param("edtId") String edtId,@Param("num") Integer num) throws Exception;
	
	/**
	 * 根据【ID】更新【购物车详情】中【作品数量】
	 * @param id 【ID】
	 * @param quantity 【作品数量】
	 * @throws SQLException
	 */
	@Update( value = { "UPDATE bud_car td SET td.num = #{quantity, javaType=Integer, jdbcType=INTEGER} WHERE td.db_id = #{id, javaType=String, jdbcType=VARCHAR}" } )
	void updateTrolleyDetailQuantityById(@Param("id") String id, @Param("quantity") int quantity) throws SQLException;
	

	/**
	 * 根据【通行证ID】查询【购物车详情总记录数】
	 * @param passportId 【通行证ID】
	 * @return 【购物车详情总记录数】
	 * @throws Exception
	 */
	@Select( value = { "SELECT COUNT(1) FROM bud_car td WHERE status = 1 AND td.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER} " } )
	Long selectTrolleyDetailTotalCountByPassportId(Integer passportId) throws SQLException;
	
	/**
	 * 根据【IDs】更新【购物车详情】的【状态】
	 * @param ids 【IDs】
	 * @param status 【状态】
	 * @throws Exception
	 */
	@Update( value = { "<script>"
			+ "UPDATE bud_car t SET t.status = #{status, javaType=Byte, jdbcType=TINYINT}"
			+ " WHERE t.db_id IN"
				+ "<foreach collection='ids' item='id' open='(' close=')' separator=','>"
					+ "#{id, javaType=String, jdbcType=VARCHAR}"
				+ "</foreach>"
			+ "</script>" } )
	void batchUpdateTrolleyDetailStatusByIds(@Param("ids") String [] ids, @Param("status") byte status) throws Exception;

	/**
	 * 根据【IDs】更新【购物车详情】的【状态】和【订单ID】
	 * @param ids 【IDs】
	 * @param status 【状态】
	 * @param orderId 【订单ID】
	 * @throws Exception
	 */
	@Update( value = { "<script>"
			+ "UPDATE bud_car td"
			+ " SET td.status = #{status, javaType=Byte, jdbcType=TINYINT}"
			+ ", td.order_id = #{orderId, javaType=String, jdbcType=VARCHAR}"
			+ " WHERE td.db_id IN"
			+ "<foreach collection='ids' item='id' open='(' close=')' separator=','>"
			+ "#{id, javaType=String, jdbcType=VARCHAR}"
			+ "</foreach>"
			+ "</script>" } )
	void batchUpdateTrolleyDetailStatusAndOrderIdByIds(@Param("ids") String [] ids, @Param("status") byte status, @Param("orderId") String orderId) throws Exception;

	/**
	 * 根据【作品ID】和【通行证ID】查询【购物车详情】
	 * @param worksId 【作品ID】
	 * @param passportId passportId
	 * @return 【购物车详情】
	 * @throws SQLException
	 */
	@Select( value = { "SELECT td.db_id AS id, td.total AS subtotal, td.num AS quantity FROM bud_car td WHERE td.status = 1 AND td.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER} AND td.edt_db_id = #{worksId, javaType=String, jdbcType=VARCHAR}" } )
	TrolleyDetail selectTrolleyDetailIdByWorksId(@Param("worksId") String worksId, @Param("passportId") int passportId) throws SQLException;
	
	/**
	 * 根据【ID】更新【购物车详情】
	 * @param trolleyDetail 【购物车详情】
	 * @throws SQLException
	 */
	@Update( value = { "<script>"
			+ " UPDATE bud_car td"
			+ " SET modify_time = NOW()"
			+ "<if test='null != price and 0 &lt; price'>"
				+ ", td.price = #{price, javaType=Float, jdbcType=FLOAT}"
			+ "</if>"
			+ "<if test='null != quantity and 0 &lt; quantity'>"
				+ ", td.num = #{quantity, javaType=Integer, jdbcType=INTEGER}"
			+ "</if>"
			+ "<if test='null != subtotal and 0 &lt; subtotal'>"
			+ ", td.total = #{subtotal, javaType=Float, jdbcType=FLOAT}"
			+ "</if>"
			+ "<if test='null != channelCode and \"\" != channelCode'>"
				+ ", td.channel_code = #{channelCode, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != clientCode and \"\" != clientCode'>"
				+ ", td.client = #{clientCode, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != couponId and \"\" != couponId'>"
				+ ", td.coupon_db_id = #{couponId, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != worksThumbnailUrl and \"\" != worksThumbnailUrl'>"
				+ ", td.thumbnail_image_url = #{worksThumbnailUrl, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != orderId and \"\" != orderId'>"
			+ ", td.order_id = #{orderId, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ " WHERE td.db_id = #{id, javaType=String, jdbcType=VARCHAR}"
			+ " </script>" } )
	void updateTrolleyDetailById(TrolleyDetail trolleyDetail) throws SQLException;

	/**
	 * 根据【IDs】批量更新【购物车详情】
	 * @param trolleyDetail 【购物车详情】
	 * @throws SQLException
	 */
	@Update( value = { "<script>"
			+ " UPDATE bud_car td"
			+ " SET modify_time = NOW()"
			+ "<if test='null != price and 0 &lt; price'>"
			+ ", td.price = #{price, javaType=Float, jdbcType=FLOAT}"
			+ "</if>"
			+ "<if test='null != quantity and 0 &lt; quantity'>"
			+ ", td.num = #{quantity, javaType=Integer, jdbcType=INTEGER}"
			+ "</if>"
			+ "<if test='null != subtotal and 0 &lt; subtotal'>"
			+ ", td.total = #{subtotal, javaType=Float, jdbcType=FLOAT}"
			+ "</if>"
			+ "<if test='null != channelCode and \"\" != channelCode'>"
			+ ", td.channel_code = #{channelCode, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != clientCode and \"\" != clientCode'>"
			+ ", td.client = #{clientCode, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != couponId and \"\" != couponId'>"
			+ ", td.coupon_db_id = #{couponId, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != worksThumbnailUrl and \"\" != worksThumbnailUrl'>"
			+ ", td.thumbnail_image_url = #{worksThumbnailUrl, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != orderId and \"\" != orderId'>"
			+ ", td.order_id = #{orderId, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ " WHERE td.db_id = #{id, javaType=String, jdbcType=VARCHAR}"
			+ " </script>" } )
	void batchUpdateTrolleyDetailByIds(List<TrolleyDetail> trolleyDetailList) throws SQLException;
}
